﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Data.SqlClient;

namespace DataServer.DAO
{
    public class DataProvider
    {
        protected static string _connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QLNV.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

        protected SqlConnection connection;
        protected SqlDataAdapter adapter;
        protected SqlCommand command;


        public static string ConnectionString
        {
            get
            {
                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        public void connect()
        {
            connection = new SqlConnection(ConnectionString);
            connection.Open();
        }

        public void disconnect()
        {
            connection.Close();
        }

        //public IDataReader executeQuery(string sqlString)
        //{
        //    command = new OleDbCommand(sqlString, connection);
        //    return command.ExecuteReader();
        //}

        public DataTable executeQuery(string sqlString)
        {
            connect();
            DataSet ds = new DataSet();
            adapter = new SqlDataAdapter(sqlString, connection);
            adapter.Fill(ds);
            disconnect();
            return ds.Tables[0];
        }

        public void executeNonQuery(string sqlString)
        {
            connect();
            command = new SqlCommand(sqlString, connection);
            command.ExecuteNonQuery();
            disconnect();

        }

        public object executeScalar(string sqlString)
        {
            command = new SqlCommand(sqlString, connection);
            return command.ExecuteScalar();
        }

        protected ArrayList ConvertDataSetToArrayList(DataSet dataset)
        {

            ArrayList arr = new ArrayList();
            DataTable dt = dataset.Tables[0];
            int i, n = dt.Rows.Count;
            for (i = 0; i < n; i++)
            {
                object obj = GetDataFromDataRow(dt, i);
                arr.Add(obj);

            }

            return arr;
        }

        protected virtual object GetDataFromDataRow(DataTable dt, int i)
        {
            return null;
        }
        public List<string> getTableNames()
        {
            connect();
            string query = "SELECT TABLE_NAME" +
                " FROM INFORMATION_SCHEMA.TABLES" +
                " WHERE (TABLE_TYPE = 'BASE TABLE')";
            List<string> tables = new List<string>();
            DataTable dt = executeQuery(query);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string table = (string)dt.Rows[i]["TABLE_NAME"];
                if (table != "sysdiagrams")
                    tables.Add(table);
            }
            //DataTable dt = connection.GetOleDbSchemaTable(
            //OleDbSchemaGuid.Tables,
            //new object[] { null, null, null, "TABLE" });
            //foreach (DataRow row in dt.Rows)
            //{
            //    string tablename = (string)row[2];
            //    tables.Add(tablename);
            //}
            disconnect();
            return tables;
        }

        public List<string> getColumnsName(string tableName)
        {
            connect();
            string query = "SELECT COLUMN_NAME" +
                " FROM INFORMATION_SCHEMA.COLUMNS" +
                " WHERE (TABLE_NAME = '"+tableName+"')";
            List<string> colName = new List<string>();

            try
            {
                DataTable dt = executeQuery(query);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string name = (string)dt.Rows[i]["COLUMN_NAME"];
                    colName.Add(name);
                }
            }
            catch (Exception)
            {
                
                throw;
            }

            return colName;
        }
    }
}